package com.atguigu.advanced;

import com.atguigu.advanced.pojo.Employee;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lhl
 * @date 2025/4/16 14:21
 */
public class JDBCAdvanced {

    @Test
    public void testORM() throws Exception {
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_emp where emp_id = ? ");

        //4.执行,接收返回结果集
        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();

        Employee employee = null;

        //5.处理结果集
        while (resultSet.next()) {
            employee = new Employee();
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            employee.setEmpId(empId);
            employee.setEmpName(empName);
            employee.setEmpSalary(empSalary);
            employee.setEmpAge(empAge);

        }
        System.out.println(employee);

        //6.释放资源（先开后关）
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testORMList() throws Exception {
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_emp ");

        //4.执行,接收返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        List<Employee> employeeList = new ArrayList<>();
        //5.处理结果集
        while (resultSet.next()) {
            Employee employee = new Employee();
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            employee.setEmpId(empId);
            employee.setEmpName(empName);
            employee.setEmpSalary(empSalary);
            employee.setEmpAge(empAge);
            employeeList.add(employee);
        }
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }


        //6.释放资源（先开后关）
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testReturnPK() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象,告知PreparedStatement，返回新增数据的主键值
        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_emp(emp_name,emp_salary,emp_age) values (?,?,?)", Statement.RETURN_GENERATED_KEYS);

        //创建对象
        Employee employee = new Employee(null,"rose",345.5,29);

        //4.执行,接收返回结果集
        preparedStatement.setString(1,employee.getEmpName());
        preparedStatement.setDouble(2,employee.getEmpSalary());
        preparedStatement.setInt(3,employee.getEmpAge());

        int row = preparedStatement.executeUpdate();
        ResultSet resultSet = null;

        //5.处理结果集
        System.out.println(row);
        if (row > 0){
            System.out.println("成功");
            resultSet = preparedStatement.getGeneratedKeys();
            if (resultSet.next()){
                employee.setEmpId(resultSet.getInt(1));
            }

            System.out.println(employee);
        }else{
            System.out.println("失败");
        }
        //6.释放资源（先开后关）
        if (resultSet != null){
            resultSet.close();
        }

        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testMoreInsert() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        String sql = "insert into t_emp(emp_name,emp_salary,emp_age) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 100; i++) {
            preparedStatement.setString(1,"mouse"+i);
            preparedStatement.setDouble(2,145.67);
            preparedStatement.setInt(3,18);

            //4.执行,接收返回结果集preparedStatement.executeUpdate();
            preparedStatement.executeUpdate();
        }


        long end = System.currentTimeMillis();

        System.out.println("消耗时间："+(end-start));

        //6.释放资源（先开后关）

        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testBatch() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?rewriteBatchedStatements=true", "root", "123456");

        //3.获取执行SQL语句的对象
        /**
         * 注意事项：
         * 1、必须在连接数据库的URL后面追加?rewriteBatchedStatements=true，允许批量操作
         * 2、新增SQL必须用values。且语句最后不要追加;结束
         * 3、调用addBatch()方法，将SQL语句进行批量添加操作
         */
        String sql = "insert into t_emp(emp_name,emp_salary,emp_age) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 100; i++) {
            preparedStatement.setString(1,"mouse"+i);
            preparedStatement.setDouble(2,145.67);
            preparedStatement.setInt(3,18);

            preparedStatement.addBatch();
        }

        preparedStatement.executeBatch();

        long end = System.currentTimeMillis();

        System.out.println("消耗时间："+(end-start));

        //6.释放资源（先开后关）

        preparedStatement.close();
        connection.close();
    }
}
